package org.kjtc.mapper;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.kjtc.entity.BackLog;

import java.util.List;

/**
 * Created by Administrator on 2017/12/6 0006.
 */
@Mapper
public interface TimerMapper {

    @Insert("insert into sprintstatistics " +
            "SELECT sp.SprintID AS SprintID, IFNULL(sum(bkl.ExpectedWorkDate),0) AS totalWorkDate,DATE(NOW()) AS date FROM sprint sp " +
            "LEFT JOIN module md ON sp.SprintID = md.SprintID " +
            "LEFT JOIN backlog bkl ON bkl.ModuleID = md.ModuleID AND bkl.BacklogStatus < 3 " +
            "WHERE NOW() BETWEEN sp.SprintStartDTTM AND sp.SprintEndDTTM GROUP BY sp.SprintID")
    int insertSprintDataEveryDay();

    @Insert("INSERT INTO workdetails SELECT bl.BacklogID,ui.UserID,ui.UserName,pd.ProductName,bl.BacklogName,st.StateName,bl.ExpectedStartDTTM,bl.ExpectedEndDTTM,bl.ExpectedWorkDate,bl.ActualStartDTTM,date_format(now(),'%y-%m-%d') " +
            "FROM backlog bl,userinfo ui,module md,productversion pv,product pd,state st " +
            "WHERE 1=1 AND bl.BacklogStatus<3 AND bl.UserID=ui.UserID AND bl.ModuleID=md.ModuleID AND pv.ProductVersionID=md.ProductVersionID " +
            "AND pv.ProductID=pd.ProductID AND st.StateID=bl.BacklogStatus " +
            "ORDER BY bl.UserID")
     int insertBackLogDataEverybody();

    @Select("select UserName,UserID from workdetails where date=date_format(now(),'%y-%m-%d') GROUP BY UserName ")
    List<BackLog> getAllMember();

    @Select("select * from workdetails WHERE date=date_format(now(),'%y-%m-%d') AND userName=#{UserName}")
    List<BackLog> getAllWorkData(String UserName);

    @Select(" SELECT bl.BacklogID,ui.UserID,ui.UserName,pd.ProductName,bl.BacklogName,st.StateName,bl.ExpectedStartDTTM,bl.ExpectedEndDTTM,bl.ExpectedWorkDate,bl.ActualStartDTTM,date_format(now(),'%y-%m-%d')" +
            " FROM backlog bl,userinfo ui,module md,productversion pv,product pd,state st" +
            " WHERE 1=1 AND bl.BacklogStatus<3 AND bl.UserID=ui.UserID AND bl.ModuleID=md.ModuleID AND pv.ProductVersionID=md.ProductVersionID" +
            " AND pv.ProductID=pd.ProductID AND st.StateID=bl.BacklogStatus AND ui.UserID=#{UserID}")
    List<BackLog> getAllWorkDataByUserID(String UserID);


}
